/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package modelo.helper;
import java.util.ArrayList;
import java.util.Iterator;
import modelo.DTO.DTODataMining;
import modelo.DTO.DTOVisitasAlumno;
import modelo.HibernateUtil;

import org.hibernate.HibernateException;
import org.hibernate.Session;
import org.hibernate.Transaction;
/**
 *
 * @version 1.1
 * @author Ion B. Padilla
 */
public class QuizHelper {
    private static QuizHelper instance;

    public QuizHelper() {
    }

    public static QuizHelper getInstance() {
        if (null == instance) {
            instance = new QuizHelper();
        }
        return instance;
    }
    /**
     * Metodo para conseguir el numero de encuestas creadas
     */
    public ArrayList<DTODataMining> getQuizPorStudents(){
        Transaction tx = null;
        ArrayList<DTODataMining> lista = new ArrayList<DTODataMining>();
        try {
            //Session session = HibernateUtil.getSessionFactory().getCurrentSession();
            Session session = HibernateUtil.getInstance().getSession();
            tx = session.beginTransaction();
            Iterator results = session.createQuery("SELECT user.username as username, COUNT(att.quiz) as tries  "
                    + "FROM MdlQuiz quiz ,MdlQuizAttempts att, MdlUser user "
                    + "WHERE  quiz.id=att.quiz  "
                    + "AND att.userid=user.id  "
                    + "GROUP BY user.id  "
                    + "ORDER BY COUNT(att.quiz)").list().iterator();
            while (results.hasNext()) {
                Object[] row = (Object[]) results.next();
                DTOVisitasAlumno alumno = new DTOVisitasAlumno((String) row[0], Long.toString((Long) row[1]));
                lista.add(alumno);
            }
            tx.commit(); session.close();
            return lista;
        } catch (HibernateException e) {
            System.out.println(e.getMessage());
            e.getStackTrace();
            return null;
        }
    }
    /**
     * Metodo para conseguir las visitas por un alumno
     * @param curso curso que queremos consultar
     */
    public ArrayList<DTODataMining> getQuizPorStudentsPorCurso(int curso){
        Transaction tx = null;
        ArrayList<DTODataMining> lista = new ArrayList<DTODataMining>();
        try {
            //Session session = HibernateUtil.getSessionFactory().getCurrentSession();
            Session session = HibernateUtil.getInstance().getSession();
            tx = session.beginTransaction();
            Iterator results = session.createQuery("SELECT user.username as username, COUNT(att.quiz) as tries  "
                    + "FROM MdlQuiz quiz ,MdlQuizAttempts att, MdlUser user "
                    + "WHERE  quiz.id=att.quiz  "
                    + "AND quiz.course="+curso+" "
                    + "AND att.userid=user.id  "
                    + "GROUP BY user.id  "
                    + "ORDER BY COUNT(att.quiz)").list().iterator();
            while (results.hasNext()) {
                Object[] row = (Object[]) results.next();
                DTOVisitasAlumno alumno = new DTOVisitasAlumno((String) row[0], Long.toString((Long) row[1]));
                lista.add(alumno);
            }
            tx.commit(); session.close();
            return lista;
        } catch (HibernateException e) {
            System.out.println(e.getMessage());
            e.getStackTrace();
            return null;
        }
    }
    /**
     * Metodo para conseguir encuestas hechas por el alumno
     * @param nombre el nombre del alumno
     */
    public DTODataMining getQuizPorStudent(String nombre) {
        DTOVisitasAlumno dtoVisitas = null;
        Transaction tx = null;
        try {
            //Session session = HibernateUtil.getSessionFactory().getCurrentSession();
            Session session = HibernateUtil.getInstance().getSession();
            tx = session.beginTransaction();
            Iterator results = session.createQuery(
                    "SELECT user.username as username, COUNT(att.quiz) as tries  "
                    + "FROM MdlQuiz quiz "
                    + "INNER JOIN MdlQuizAttempts att "
                    + "ON  quiz.id=att.quiz "
                    + "INNER JOIN MdlUser user "
                    + "ON att.userid=user.id "
                    + " WHERE user.username= '"+nombre+"' "
                    + "GROUP BY user.id "
                    + "ORDER BY COUNT(att.quiz)").list().iterator();
            while (results.hasNext()) {
                Object[] row = (Object[]) results.next();
                dtoVisitas = new DTOVisitasAlumno((String) row[0], Long.toString((Long) row[1]));
            }
            tx.commit(); session.close();
            return dtoVisitas;
        } catch (HibernateException e) {
            System.out.println(e.getMessage());
            e.getStackTrace();
            return null;
        }
    }
    /**
     * Metodo para conseguir encuestas hechas por el alumno por curso
     * @param nombre el nombre del alumno
     * @param curso curso en el que hacer la consulta
     */
    public DTODataMining getQuizPorStudentPorCurso(String nombre,int curso) {
        DTOVisitasAlumno dtoVisitas = null;
        Transaction tx = null;
        try {
            //Session session = HibernateUtil.getSessionFactory().getCurrentSession();
            Session session = HibernateUtil.getInstance().getSession();
            tx = session.beginTransaction();
            Iterator results = session.createQuery(
                    "SELECT user.username as username, COUNT(att.quiz) as tries  "
                    + "FROM MdlQuiz quiz "
                    + "INNER JOIN MdlQuizAttempts att "
                    + "ON  quiz.id=att.quiz "
                    + "INNER JOIN MdlUser user "
                    + "ON att.userid=user.id "
                    + " WHERE user.username= '"+nombre+"' "
                    + " AND quiz.course="+curso+" "
                    + "GROUP BY user.id "
                    + "ORDER BY COUNT(att.quiz)").list().iterator();
            while (results.hasNext()) {
                Object[] row = (Object[]) results.next();
                dtoVisitas = new DTOVisitasAlumno((String) row[0], Long.toString((Long) row[1]));
            }
            tx.commit(); session.close();
            return dtoVisitas;
        } catch (HibernateException e) {
            System.out.println(e.getMessage());
            e.getStackTrace();
            return null;
        }
    }
    /**
     * Metodo para conseguir las encuestas aprobadas
     */
    public ArrayList<DTODataMining> getQuizAprobadasPorStudents(){
        Transaction tx = null;
        ArrayList<DTODataMining> lista = new ArrayList<DTODataMining>();
        try {
            //Session session = HibernateUtil.getSessionFactory().getCurrentSession();
            Session session = HibernateUtil.getInstance().getSession();
            tx = session.beginTransaction();
            Iterator results = session.createQuery("SELECT user.username as username, COUNT(user.username) as numpassed "
                    + "FROM MdlQuiz quiz "
                    + "INNER JOIN MdlQuizGrades gr "
                    + "ON quiz.id=gr.quiz "
                    + "INNER JOIN MdlUser user "
                    + "ON gr.userid=user.id "
                    + "WHERE gr.grade>5 "
                    + "GROUP BY user.username "
                    + "ORDER BY COUNT(user.username)").list().iterator();
            while (results.hasNext()) {
                Object[] row = (Object[]) results.next();
                DTOVisitasAlumno alumno = new DTOVisitasAlumno((String) row[0], Long.toString((Long) row[1]));
                lista.add(alumno);
            }
            tx.commit(); session.close();
            return lista;
        } catch (HibernateException e) {
            System.out.println(e.getMessage());
            e.getStackTrace();
            return null;
        }
    }
    /**
     * Metodo para conseguir las encuestas aprobadas por curso
     * @param curso el curso que se quiere consultar
     */
    public ArrayList<DTODataMining> getQuizAprobadasPorStudentsPorCurso(int curso){
        Transaction tx = null;
        ArrayList<DTODataMining> lista = new ArrayList<DTODataMining>();
        try {
            //Session session = HibernateUtil.getSessionFactory().getCurrentSession();
            Session session = HibernateUtil.getInstance().getSession();
            tx = session.beginTransaction();
            Iterator results = session.createQuery("SELECT user.username as username, COUNT(user.username) as numpassed "
                    + "FROM MdlQuiz quiz "
                    + "INNER JOIN MdlQuizGrades gr "
                    + "ON quiz.id=gr.quiz "
                    + "INNER JOIN MdlUser user "
                    + "ON gr.userid=user.id "
                    + "WHERE gr.grade>5 "
                    + "AND quiz.course="+curso+" "
                    + "GROUP BY user.username "
                    + "ORDER BY COUNT(user.username)").list().iterator();
            while (results.hasNext()) {
                Object[] row = (Object[]) results.next();
                DTOVisitasAlumno alumno = new DTOVisitasAlumno((String) row[0], Long.toString((Long) row[1]));
                lista.add(alumno);
            }
            tx.commit(); session.close();
            return lista;
        } catch (HibernateException e) {
            System.out.println(e.getMessage());
            e.getStackTrace();
            return null;
        }
    }
    /**
     * Metodo para conseguir las encuestas aprobadas por alumno
     * @param nombre nombre del alumno
     */
    public DTODataMining getQuizAprobadasPorStudent(String nombre) {
        DTOVisitasAlumno dtoVisitas = null;
        Transaction tx = null;
        try {
            //Session session = HibernateUtil.getSessionFactory().getCurrentSession();
            Session session = HibernateUtil.getInstance().getSession();
            tx = session.beginTransaction();
            Iterator results = session.createQuery(
                    "SELECT user.username as username, COUNT(user.username) as numpassed "
                    + "FROM MdlQuiz quiz "
                    + "INNER JOIN MdlQuizGrades gr "
                    + "ON quiz.id=gr.quiz "
                    + "INNER JOIN MdlUser user "
                    + "ON gr.userid=user.id "
                    + "WHERE gr.grade>5 "
                    + "AND user.username='"+nombre+"' "
                    + "GROUP BY user.username "
                    + "ORDER BY COUNT(user.username)").list().iterator();
            while (results.hasNext()) {
                Object[] row = (Object[]) results.next();
                dtoVisitas = new DTOVisitasAlumno((String) row[0], Long.toString((Long) row[1]));
            }
            tx.commit(); session.close();
            return dtoVisitas;
        } catch (HibernateException e) {
            System.out.println(e.getMessage());
            e.getStackTrace();
            return null;
        }
    }
    /**
     * Metodo para conseguir las encuestas aprobadas por alumno
     * @param nombre nombre del alumno
     * @param curso el curso que se quiere consultar
     */
    public DTODataMining getQuizAprobadasPorStudentPorCurso(String nombre,int curso) {
        DTOVisitasAlumno dtoVisitas = null;
        Transaction tx = null;
        try {
            //Session session = HibernateUtil.getSessionFactory().getCurrentSession();
            Session session = HibernateUtil.getInstance().getSession();
            tx = session.beginTransaction();
            Iterator results = session.createQuery(
                    "SELECT user.username as username, COUNT(user.username) as numpassed "
                    + "FROM MdlQuiz quiz "
                    + "INNER JOIN MdlQuizGrades gr "
                    + "ON quiz.id=gr.quiz "
                    + "INNER JOIN MdlUser user "
                    + "ON gr.userid=user.id "
                    + "WHERE gr.grade>5 "
                    + "AND user.username='"+nombre+"' "
                    + "AND quiz.course="+curso+" "
                    + "GROUP BY user.username "
                    + "ORDER BY COUNT(user.username)").list().iterator();
            while (results.hasNext()) {
                Object[] row = (Object[]) results.next();
                dtoVisitas = new DTOVisitasAlumno((String) row[0], Long.toString((Long) row[1]));
            }
            tx.commit(); session.close();
            return dtoVisitas;
        } catch (HibernateException e) {
            System.out.println(e.getMessage());
            e.getStackTrace();
            return null;
        }
    }
}
